﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data.SqlClient;
using System.Data;
namespace CoppyDatabase.Commons
{
    public static class clsExecuteQuery
    {
        
            #region Khai báo biến SQL
            private static SqlConnection sql_con;
            private static SqlCommand sql_cmd;
            private static SqlDataAdapter sql_Ad;
            private static DataSet DS = new DataSet();
            private static DataTable DT = new DataTable();
            #endregion

            #region Khai báo biến MySQL
            private static MySqlConnection MySql_con;
            private static MySqlCommand MySql_cmd;
            private static MySqlDataAdapter MySql_Ad;
            private static DataSet MySql_DS = new DataSet();
            private static DataTable MySql_DT = new DataTable();
            #endregion

            public static bool CanOpen_MySql(MySqlConnection connection)
            {
                try
                {
                    if (connection == null) { return false; }
                    connection.Open();
                    var canOpen = connection.State == ConnectionState.Open;
                    connection.Close();
                    return canOpen;
                }
                catch
                {
                    return false;
                }
            }
            public static bool CanOpen_Sql(SqlConnection connection)
            {
                try
                {
                    if (connection == null) { return false; }
                    connection.Open();
                    var canOpen = connection.State == ConnectionState.Open;
                    connection.Close();
                    return canOpen;
                }
                catch
                {
                    return false;
                }
            }
            public static DataTable SelectSql_ReturnTable(SqlConnection connection, string strSql)
            {
                connection.Open();
                DataTable dtTablesName = new DataTable();
                sql_Ad = new SqlDataAdapter(strSql, connection);
                sql_Ad.Fill(dtTablesName);
                connection.Close();
                return dtTablesName;
            }
            public static DataTable SelectMySql_ReturnTable(MySqlConnection connection, string strMySql)
            {
                connection.Open();
                DataTable dtTablesName = new DataTable();
                MySql_Ad = new MySqlDataAdapter(strMySql, connection);
                MySql_Ad.Fill(dtTablesName);
                connection.Close();
                return dtTablesName;
            }
            public static DataTable InformationTable(SqlConnection connection, string tableName)
            {
                string strDetailTable = "SELECT TABLE_CATALOG, TABLE_SCHEMA ,COLUMN_NAME,DATA_TYPE ,CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE,COLUMN_DEFAULT ";
                strDetailTable += " FROM INFORMATION_SCHEMA.COLUMNS";
                strDetailTable += " WHERE TABLE_NAME = '" + tableName + "'";
                strDetailTable += " ORDER BY ORDINAL_POSITION";

                connection.Open();
                //sql_cmd = sql_con.CreateCommand();
                DataTable dtTablesName = new DataTable();
                sql_Ad = new SqlDataAdapter(strDetailTable, connection);
                sql_Ad.Fill(dtTablesName);
                connection.Close();
                return dtTablesName;
            }

            public static DataTable ColumnPrimaryKey(SqlConnection connection, string tableName)
            {
                string strDetailTable = "SELECT column_name as PrimaryKey ";
                strDetailTable += " FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE ";
                strDetailTable += " WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') =1";
                strDetailTable += " AND table_name = '" + tableName + "'";

                connection.Open();
                DataTable dtTablesName = new DataTable();
                sql_Ad = new SqlDataAdapter(strDetailTable, connection);
                sql_Ad.Fill(dtTablesName);
                connection.Close();
                return dtTablesName;
            }

            public static DataTable ColumnIdentity(SqlConnection connection, string tableName)
            {
                string strDetailTable = "SELECT OBJECT_NAME(OBJECT_ID) AS TABLENAME,NAME AS COLUMNNAME,SEED_VALUE,increment_value ";
                strDetailTable += " FROM SYS.IDENTITY_COLUMNS ";
                strDetailTable += " where SEED_VALUE > 0 ";
                strDetailTable += " AND OBJECT_NAME(OBJECT_ID) = '" + tableName + "'";

                connection.Open();
                //sql_cmd = sql_con.CreateCommand();
                DataTable dtTablesName = new DataTable();
                sql_Ad = new SqlDataAdapter(strDetailTable, connection);
                sql_Ad.Fill(dtTablesName);
                connection.Close();
                return dtTablesName;
            }
            public static DataTable getRelationship_Sql(SqlConnection connection)
            {
                string strRelationship = " SELECT distinct tp.name 'Parent table',cp.name, cp.column_id,tr.name 'Refrenced table',cr.name 'Refrenced column', cr.column_id";
                strRelationship += " FROM sys.foreign_keys fk";
                strRelationship += " INNER JOIN sys.tables tp ON fk.parent_object_id = tp.object_id";
                strRelationship += " INNER JOIN sys.tables tr ON fk.referenced_object_id = tr.object_id";
                strRelationship += " INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id";
                strRelationship += " INNER JOIN sys.columns cp ON fkc.parent_column_id = cp.column_id AND fkc.parent_object_id = cp.object_id";
                strRelationship += " INNER JOIN sys.columns cr ON fkc.referenced_column_id = cr.column_id AND fkc.referenced_object_id = cr.object_id";
                strRelationship += " ORDER BY tp.name, cp.column_id";

                connection.Open();
                //sql_cmd = sql_con.CreateCommand();
                DataTable dtTablesName = new DataTable();
                sql_Ad = new SqlDataAdapter(strRelationship, connection);
                sql_Ad.Fill(dtTablesName);
                connection.Close();
                return dtTablesName;
            }
            public static DataTable getRelationshipMy_Sql(SqlConnection connection)
            {
                string strRelationship = " SELECT distinct tp.name 'Parent table',cp.name, cp.column_id,tr.name 'Refrenced table',cr.name 'Refrenced column', cr.column_id";
                strRelationship += " FROM sys.foreign_keys fk";
                strRelationship += " INNER JOIN sys.tables tp ON fk.parent_object_id = tp.object_id";
                strRelationship += " INNER JOIN sys.tables tr ON fk.referenced_object_id = tr.object_id";
                strRelationship += " INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id";
                strRelationship += " INNER JOIN sys.columns cp ON fkc.parent_column_id = cp.column_id AND fkc.parent_object_id = cp.object_id";
                strRelationship += " INNER JOIN sys.columns cr ON fkc.referenced_column_id = cr.column_id AND fkc.referenced_object_id = cr.object_id";
                strRelationship += " ORDER BY tp.name, cp.column_id";

                connection.Open();
                //sql_cmd = sql_con.CreateCommand();
                DataTable dtTablesName = new DataTable();
                sql_Ad = new SqlDataAdapter(strRelationship, connection);
                sql_Ad.Fill(dtTablesName);
                connection.Close();
                return dtTablesName;
            }
        public static DataTable deleteAllTable_foreign(SqlConnection connection)
            {
                //string str_foreign = " select distinct tr.name 'Table foreign_keys'";
                //str_foreign += " from  sys.foreign_keys fk";
                //str_foreign += " INNER JOIN sys.tables tr ON fk.referenced_object_id = tr.object_id";

                string str_foreign = " select distinct tp.name 'Table foreign_keys'";
                str_foreign += " from  sys.foreign_keys fk";
                str_foreign += " INNER JOIN sys.tables tp ON fk.parent_object_id = tp.object_id";

                connection.Open();
                //sql_cmd = sql_con.CreateCommand();
                DataTable dtTablesName = new DataTable();
                sql_Ad = new SqlDataAdapter(str_foreign, connection);
                sql_Ad.Fill(dtTablesName);
                connection.Close();
                return dtTablesName;
            }

        
 
 
            public static  void DropTable(MySqlConnection connection, string tableName)
            {
                MySql_con = connection;
                string strDelete = "drop table " + tableName;
                ExecuteMySqlQuery(MySql_con,strDelete);
            }
            public static void DropDatabase(MySqlConnection connection, string DatabaseName)
            {
                MySql_con = connection;
                string strDelete = "drop DATABASE " + DatabaseName;
                ExecuteMySqlQuery(MySql_con, strDelete);
            }
            public static void CreateDatabase(MySqlConnection connection, string DatabaseName)
            {
                MySql_con = connection;
                string strCreate = "Create DATABASE " + DatabaseName;
                ExecuteMySqlQuery(MySql_con, strCreate);
            }

            
            public static void ExecuteQuery(string txtQuery)
            {
                sql_con.Open();
                sql_cmd = sql_con.CreateCommand();
                sql_cmd.CommandText = txtQuery;
                sql_cmd.ExecuteNonQuery();
                sql_con.Close();
            }

            public static void ExecuteMySqlQuery(MySqlConnection connection, string txtQuery)
            {

                if (connection.State.ToString() == "Closed" || connection.State.ToString() == "false")
                {
                    connection.Open();
                }
                MySql_cmd = connection.CreateCommand();
                MySql_cmd.CommandText = txtQuery;
                MySql_cmd.ExecuteNonQuery();
                connection.Close();
            }
            public static Boolean CheckTableExist(MySqlConnection connection,string DatabaseName, string tableName)
            { 
                string strCheckExist = "SELECT * FROM information_schema.tables WHERE table_schema = '"+DatabaseName +"' AND table_name = '"+tableName +"'";
                if (connection.State.ToString() == "Closed" || connection.State.ToString() == "false")
                {
                    connection.Open();
                }
                //sql_cmd = sql_con.CreateCommand();
                DataTable dtTablesName = new DataTable();
                MySql_Ad = new MySqlDataAdapter(strCheckExist, connection);
                MySql_Ad.Fill(dtTablesName);
                connection.Close();

                if (Common.ParseInt(dtTablesName.Rows.Count) > 0)
                {
                    return true;
                }
                else
                { return false; }
            }
        
    }
}
